<?php
/**
 * Desc: 公共服务类
 * Auth: Shaoming Lu<lushao1012@163.com>
 * Date: 2018/5/29
 * Time: 16:23
 */
namespace App\Http\Services;

class DBMysqli
{
    private $conn;
    private $sql;
    private $result;
    private $host;
    private $username;
    private $password;
    private $port;
    private $connect_data;
    private $db;
    private $field;

    public function __construct($db = 'lab')
    {
        $data = session('db');
//        var_dump($data);exit;
        $this->connect_data = $data;
        $this->host = $data['host'];
        $this->username = $data['username'];
        $this->password = $data['password'];
        $this->port = $data['port'];
        $this->db = $db;
        $this->conn = null;
        $this->sql = '';
        $this->result = null;
        $this->field = null;
    }

    public function __destruct()
    {
        // TODO: Implement __destruct() method.
        if($this->conn){
            mysqli_close($this->conn);
        }
    }

    public function connect_mysql()
    {
        try{
            if ($this->db) {
                $conn = mysqli_connect("'".$this->host."'", $this->username, $this->password, $this->db);
            } else {
                $conn = mysqli_connect("'".$this->host."'", $this->username, $this->password);
            }

        }catch (\Exception $e) {
//            AjaxService::ajax_return(['error' => "Can't connect to MySQL Server. ErrorMsg:  " . mysqli_connect_error()]);
            return ['status' => 500, 'error_msg' => "Can't connect to MySQL Server. ErrorMsg:  " . mysqli_connect_error()];
        }

        // 设置字符集
        mysqli_query($conn, "set names 'utf-8' ");
        mysqli_query($conn, "set character_set_client=utf-8");
        mysqli_query($conn, "set character_set_results=utf-8");
        $this->conn = $conn;
        return true;
    }

    /**
     * 执行原生插入、更新、删除等操作的语句，即不是查询语句
     * @access  public
     * @author  Lu Shaoming<lushao1012@163.com>
     * @method  GET
     * @param $sql
     * @return array|bool|\mysqli_result
     */
    public function execute_sql($sql)
    {
        $this->sql = $sql;
        $result = mysqli_query($this->conn, $sql);
        if ($result === false) {
            return ['status' => 500, 'error_msg' => mysqli_error($this->conn) . '. The SQL: ' . $sql];
        }
        return $result;
    }

    /**
     * 执行原生SQL语句,只返回数据行
     * @access  public
     * @author  Lu Shaoming<lushao1012@163.com>
     * @method  GET
     * @param $sql
     * @return array
     */
    public function query_sql_1($sql)
    {
        $this->sql = $sql;
        $result = mysqli_query($this->conn, $sql);
        if ($result === false) {
            return ['status' => 500, 'error_msg' => mysqli_error($this->conn) . '. The SQL: ' . $sql];
        }
        $res = [];
        while ($row = mysqli_fetch_assoc($result)) {
            $res[] = $row;
        }
        return $res;
    }

    /**
     * 执行原生SQL语句，返回表结构和数据行
     * @access  public
     * @author  Lu Shaoming<lushao1012@163.com>
     * @method  GET
     * @param $sql
     * @return array
     */
    public function query_sql($sql)
    {
        $this->sql = $sql;
        $result = mysqli_query($this->conn, $sql);
        if ($result === false) {
            return ['status' => 500, 'error_msg' => mysqli_error($this->conn) . '. The SQL: ' . $sql];
        }

        $res = [];
        $field = [];
        $fields = mysqli_num_fields($result);
        while ($field_info = mysqli_fetch_field($result)) {
            $field[] = $field_info;
        }
        while ($row = mysqli_fetch_assoc($result)) {
            $res[] = $row;
        }
        $field = json_decode(json_encode($field), true);
        $this->result = $res;
        $this->field = $field;
        $ret['field'] = $field;
        $ret['result'] = $res;
        return $ret;
    }

    /**
     * 查询全部数据库
     * @access  public
     * @author  Lu Shaoming<lushao1012@163.com>
     * @method  GET
     * @return array
     */
    public function show_all_databases()
    {
        $res = $this->query_sql('show databases');
        return $res;
    }

    /**
     * @access  public
     * @author  Lu Shaoming<lushao1012@163.com>
     * @method  GET
     * @param $dbname
     * @return array
     */
    public function show_all_tables($db_name = '')
    {
        if ($db_name) {
            $this->query_sql('use '.$db_name);
        }
//        $res = $this->query_sql('show tables');
        $res = $this->query_sql("select lower(table_name) as table_name,lower(table_comment)as table_comment from information_schema.tables where table_schema = '".$this->db."'");
        return $res;
    }

    /**
     * 获取字段名称和注释
     * @access  public
     * @author  Lu Shaoming<lushao1012@163.com>
     * @method  GET
     * @param $table
     * @param string $field
     * @return array
     */
    public function get_field_comment($table, $field = '*')
    {
        $field = $this->merge_spaces($field);
        if ($field == '*') {
            $sql = "select lower(column_name) as column_name, lower(column_comment) as column_comment from information_schema.columns where table_schema ='".$this->db."' and table_name = '".$table."'";
        } else {
            $sql = "select column_name, column_comment from information_schema.columns where table_schema ='".$this->db."' and table_name = '".$table."' and column_name in ('')";
        }
        $res = $this->query_sql_1($sql);
        return $res;
    }

    /**
     * @access  public
     * @author  Lu Shaoming<lushao1012@163.com>
     * @method  GET
     * @param $table
     * @param string $field
     * @param string $order
     * @param int $page
     * @param int $limit
     * @param int $is_include_field
     * @return array
     */
    public function query_list($table, $field = '*', $order = '', $page = 1, $limit = 25, $is_include_field = 0)
    {
        $order_by = '';
        if ($order) {
            $order_by = "order by ".$order;
        }
        $sql = "select ".$field." from ".$table." ".$order_by." limit ".($page-1)*$limit.",".$limit."";
        if ($is_include_field == 0) {
            $res = $this->query_sql_1($sql);
        } else {
            $res = $this->query_sql($sql);
        }

        return $res;
    }

    /**
     * @access  public
     * @author  Lu Shaoming<lushao1012@163.com>
     * @method  GET
     * @param $table
     * @param string $where
     * @param string $field
     * @return array
     */
    public function get_total_rows($table, $where = '', $field = '*')
    {
        $where_sql = '';
        if ($where) {
            $where_sql = " where ".$where;
        }
        $sql = "select count(*) as total from ".$table.$where_sql;
        $res = $this->query_sql_1($sql);
        if (isset($res['status'])) {
            return $res;
        }
        return $res[0]['total'];
    }

    public function show_all_charset()
    {
        $sql = 'SHOW CHARACTER SET';
        $res = $this->query_sql_1($sql);
        return $res;
    }

    public function merge_spaces($string)
    {
        return preg_replace ( "/\s(?=\s)/","\\1", $string );
    }


}